<?php
// +----------------------------------------------------------------------
//            -------------------------
//           /   / ----------------\  \
//          /   /             \  \
//         /   /              /  /
//        /   /    /-------------- /  /
//       /   /    /-------------------\  \
//      /   /                   \  \
//     /   /                     \  \
//    /   /                      /  /
//   /   /      /----------------------- /  /
//  /-----/      /---------------------------/
// +----------------------------------------------------------------------
// | Copyright (c) 2016 http://baimifan.cn All rights reserved.
// +----------------------------------------------------------------------
// | Author: Sage 2016/8/1  9:26
// +----------------------------------------------------------------------
namespace Service;
class ExcelService {
    const Directory='uploads/excel/';//存放文件夹
    function __construct($container) {
        $this->di=$container;
        $this->color_title='20376400';//字体颜色-标题
        $this->color_table='FF993300';//字体颜色-内容
    }
    //根据数据导出excel
    function export($data,$format=true){
        //导入
        $this->requireExcelPHP();
        //创建PHPExcel对象，注意，不能少了\
        $objPHPExcel = new \PHPExcel();
        //    设置当前的sheet
        $objPHPExcel->setActiveSheetIndex(0);
        //    设置sheet的name
        $objPHPExcel->getActiveSheet()->setTitle('Data');
        //    设置单元格的值
        $letter=1;
        $num=1;

        //头部-字段名
        foreach($data[0] as $k=>$v){
            $index=$this->convert_10to26($letter).$num;
            $objPHPExcel->getActiveSheet()->getColumnDimension($this->convert_10to26($letter))->setWidth(16);
            if($format){
                $objPHPExcel->getActiveSheet()->getStyle($index)->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_WHITE);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getFill()->getStartColor()->setARGB($this->color_title);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
            }

            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getTop()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getTop()->getColor()->setARGB($this->color_table);
            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getLeft()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getLeft()->getColor()->setARGB($this->color_table);
            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getBottom()->getColor()->setARGB($this->color_table);
            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getRight()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
            $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getRight()->getColor()->setARGB($this->color_table);

            $objPHPExcel->getActiveSheet()->getStyle($index)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getActiveSheet()->setCellValue($index, $v);
            $letter++;
        }
        unset($data[0]);
        $num++;
        foreach($data as $k=>$v){
            $letter=1;
            foreach($data[$k] as $kk=>$vv){
                $index=$this->convert_10to26($letter).$num;
                if($vv=='NULL'){
                    $vv='';
                }
                $objPHPExcel->getActiveSheet()->getStyle($index)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getLeft()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getLeft()->getColor()->setARGB($this->color_table);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getBottom()->getColor()->setARGB($this->color_table);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getRight()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getBorders()->getRight()->getColor()->setARGB($this->color_table);

                $objPHPExcel->getActiveSheet()->setCellValueExplicit($index,$vv,\PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->getStyle($index)->getNumberFormat()->setFormatCode("@");

                $letter++;
            }
            $num++;
        }

//        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);//    保存excel—2007格式 .xlsx
        $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); //或者非2007格式 .xls格式
        //保存文件夹名
        $filename = 'export_' . date('YmdHis') . ".xls";
        $filepath = self::Directory . $filename;
        $objWriter->save($filepath);
        return $filepath;
    }
    //导入ExcelPHP类别
    function requireExcelPHP(){
        //不同框架或环境调整导入即可
        import("Common.PHPExcel.PHPExcel" , '' , '.php');
        import("Common.PHPExcel.PHPExcel.Writer.Excel2007" , '' , '.php');
    }

    //10进制转换26进制
    //1=A,27=AA
    function convert_10to26($x){
        if($x<=26){
            return  chr($x+ord('A')-1);
        }
        $m=floor($x/26);
        $n=$x-$m*26;
        $x=($x-$n)/26;
        return convert_10to26($x).chr($n+ord('A')-1);
    }
    //excel时间格式转换
    function convert_etimetoptime($date, $time=false){
        if(is_numeric($date)){
            $jd = GregorianToJD(1, 1, 1970);
            $gregorian = JDToGregorian($jd+intval($date)-25569);
            $date = explode('/',$gregorian);
            $date_str = str_pad($date[2],4,'0', STR_PAD_LEFT)
                ."-".str_pad($date[0],2,'0', STR_PAD_LEFT)
                ."-".str_pad($date[1],2,'0', STR_PAD_LEFT)
                .($time?" 00:00:00":'');
            return $date_str;
        }
        return $date;
    }
}